#! /bin/bash
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
set hive.exec.orc.compression.strategy=COMPRESSION;

DROP TABLE edu_dwd.dwd_dim_customer;
DROP TABLE edu_dwd.dwd_dim_customer_appeal;
DROP TABLE edu_dwd.dwd_dim_customer_clue;
DROP TABLE edu_dwd.dwd_dim_employee;
DROP TABLE edu_dwd.dwd_dim_itcast_school;
DROP TABLE edu_dwd.dwd_dim_scrm_department;
DROP TABLE edu_dwd.dwd_dim_itcast_subject;
DROP TABLE edu_dwd.dwd_fact_customer_relationship;


-- 客户静态信息表
CREATE TABLE edu_dwd.dwd_dim_customer
(
    id               INT        COMMENT '学员id',
    area             STRING     COMMENT '所在区域',
    create_date_time STRING     COMMENT '建表时间',
    update_date_time STRING     COMMENT '最后更新时间'
)
COMMENT '客户静态信息表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

INSERT INTO TABLE edu_dwd.dwd_dim_customer
SELECT
    id,
    area,
    SUBSTR(create_date_time,0,19) AS create_date_time,
    SUBSTR(update_date_time,0,19) AS update_date_time
FROM edu_ods.ods_dim_customer

WHERE deleted =false;


--客户线索申诉信息表
CREATE TABLE if not exists edu_dwd.dwd_dim_customer_appeal
(
    customer_relationship_first_id INT    COMMENT '第一条客户关系id',
    appeal_status                  INT    COMMENT '申诉状态，0:待稽核 1:无效 2：有效',
    create_date_time               STRING COMMENT '创建时间（申诉时间）',
    update_date_time               STRING COMMENT '更新时间'
)
COMMENT '线索申诉信息表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

INSERT INTO TABLE edu_dwd.dwd_dim_customer_appeal
SELECT
    customer_relationship_first_id,
    appeal_status,
    SUBSTR(create_date_time,0,19) AS create_date_time,
    SUBSTR(update_date_time,0,19) AS update_date_time
FROM edu_ods.ods_dim_customer_appeal
WHERE deleted =false;

--客户线索表
CREATE TABLE if not exists edu_dwd.dwd_dim_customer_clue
(
    customer_relationship_id       INT    COMMENT '客户关系id',
    clue_state                     STRING COMMENT '线索状态',
    create_date_time               STRING COMMENT '创建时间（申诉时间）',
    update_date_time               STRING COMMENT '更新时间'
)
COMMENT '客户线索表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

INSERT INTO TABLE edu_dwd.dwd_dim_customer_clue
SELECT
    customer_relationship_id,
    clue_state,
    SUBSTR(create_date_time,0,19) AS create_date_time,
    SUBSTR(update_date_time,0,19) AS update_date_time
FROM edu_ods.ods_dim_customer_clue
WHERE deleted = FALSE;

--客户意向表
CREATE TABLE if not exists edu_dwd.dwd_fact_customer_relationship
(
    id                INT           COMMENT '意向id',
    customer_id       INT           COMMENT '所属客户id',
    origin_type       STRING        COMMENT '数据来源',
    itcast_school_id  INT           COMMENT '校区Id',
    itcast_subject_id INT           COMMENT '学科Id',
    origin_channel    STRING        COMMENT '来源渠道',
    creator           INT           COMMENT '创建人',
    create_date_time  STRING        COMMENT '创建时间（申诉时间）',
    update_date_time  STRING        COMMENT '更新时间',
    create_time_year  STRING        COMMENT '年',
    create_time_month STRING        COMMENT '月',
    create_time_hour  STRING        COMMENT '小时'
)
COMMENT '客户意向表'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

INSERT INTO TABLE edu_dwd.dwd_fact_customer_relationship PARTITION(dt)
SELECT
    id,
    customer_id,
    origin_type,
    itcast_school_id,
    itcast_subject_id,
    origin_channel,
    creator,
    SUBSTR(create_date_time,0,19) AS create_date_time,
    SUBSTR(update_date_time,0,19) AS update_date_time,
    substr(create_date_time,1,4) as create_time_year,
    substr(create_date_time,1,7) as create_time_month,
    substr(create_date_time,1,13) as create_time_hour,
    substr(create_date_time,1,10) as dt
FROM edu_ods.ods_fact_customer_relationship
--实战流程上说明此表deleted为1则表示删除
WHERE deleted != 1;

--员工信息表
CREATE TABLE edu_dwd.dwd_dim_employee
(
    id                  INT     COMMENT '员工id',
    tdepart_id          INT     COMMENT '直属部门',
    create_date_time    STRING  COMMENT '创建时间（申诉时间）',
    update_date_time    STRING  COMMENT '更新时间'
)
    COMMENT '员工信息表'
    row format delimited fields terminated by '\t';

INSERT INTO TABLE edu_dwd.dwd_dim_employee
SELECT
    id,
    tdepart_id,
    SUBSTR(create_date_time,0,19) AS create_date_time,
    SUBSTR(update_date_time,0,19) AS update_date_time
FROM edu_ods.ods_dim_employee
--实战流程上说明此表deleted为1则表示删除
WHERE deleted = FALSE;



--校区信息表
CREATE TABLE IF NOT EXISTS edu_dwd.dwd_dim_itcast_school
(
    id                  INT        COMMENT '校区id',
    name                string     COMMENT '校区名称',
    create_date_time    STRING     COMMENT '创建时间（申诉时间）',
    update_date_time    STRING     COMMENT '更新时间'

)
    COMMENT '校区信息表'
    row format delimited fields terminated by '\t';

INSERT INTO TABLE edu_dwd.dwd_dim_itcast_school
SELECT
    id,
    name,
    SUBSTR(create_date_time,0,19) AS create_date_time,
    SUBSTR(update_date_time,0,19) AS update_date_time
FROM edu_ods.ods_dim_itcast_school
WHERE deleted = FALSE;

--学科信息表
CREATE TABLE IF NOT EXISTS edu_dwd.dwd_dim_itcast_subject
(
    id                  INT        COMMENT '学科id',
    name                string     COMMENT '学科名称',
    create_date_time    STRING     COMMENT '创建时间（申诉时间）',
    update_date_time    STRING     COMMENT '更新时间'
)
COMMENT '学科信息表'
row format delimited fields terminated by '\t';
INSERT INTO TABLE edu_dwd.dwd_dim_itcast_subject
SELECT
    id,
    name,
    SUBSTR(create_date_time,0,19) AS create_date_time,
    SUBSTR(update_date_time,0,19) AS update_date_time
FROM edu_ods.ods_dim_tcast_subject
WHERE deleted = FALSE;

--员工部门表
CREATE TABLE IF NOT EXISTS edu_dwd.dwd_dim_scrm_department
(
    id                      INT            COMMENT '部门id',
    name                    string         COMMENT '部门名称',
    create_date_time        STRING         COMMENT '创建时间（申诉时间）',
    update_date_time        STRING         COMMENT '更新时间'
)
COMMENT '员工部门表'
row format delimited fields terminated by '\t';

INSERT INTO TABLE edu_dwd.dwd_dim_scrm_department
SELECT
    id,
    name,
    SUBSTR(create_date_time,0,19) AS create_date_time,
    SUBSTR(update_date_time,0,19) AS update_date_time
FROM edu_ods.ods_dim_scrm_department
WHERE deleted = FALSE;








